import mysql.connector
from mysql.connector import Error, pooling
from mysql.connector.cursor import MySQLCursorDict

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'database': 'test_db',  # 数据库名
    'user': 'root',         # 用户名
    'password': 'your_password',  # 密码
    'port': 3306,
    'charset': 'utf8mb4'
}

def create_database():
    """创建数据库（如果不存在）"""
    connection = None
    try:
        # 先连接到MySQL服务器（不指定数据库）
        connection = mysql.connector.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password']
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            # 创建数据库
            cursor.execute("CREATE DATABASE IF NOT EXISTS test_db")
            print("数据库 'test_db' 创建成功或已存在")
            
    except Error as e:
        print(f"创建数据库时出错: {e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def create_table():
    """创建表（如果不存在）"""
    connection = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        
        if connection.is_connected():
            cursor = connection.cursor()
            # 创建用户表
            create_table_query = """
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                age INT,
                email VARCHAR(100) UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            cursor.execute(create_table_query)
            connection.commit()
            print("表 'users' 创建成功或已存在")
            
    except Error as e:
        print(f"创建表时出错: {e}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def insert_data():
    """插入数据（单条和批量）"""
    connection = None
    try:
        connection = mysql.connector.connect(** DB_CONFIG)
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 单条插入
            single_insert_query = """
            INSERT INTO users (name, age, email)
            VALUES (%s, %s, %s)
            """
            user_data = ("张三", 25, "zhangsan@example.com")
            cursor.execute(single_insert_query, user_data)
            
            # 批量插入
            batch_insert_query = """
            INSERT INTO users (name, age, email)
            VALUES (%s, %s, %s)
            """
            users = [
                ("李四", 30, "lisi@example.com"),
                ("王五", 28, "wangwu@example.com"),
                ("赵六", 35, "zhaoliu@example.com")
            ]
            cursor.executemany(batch_insert_query, users)
            
            connection.commit()
            print(f"成功插入 {cursor.rowcount} 条数据")
            
    except Error as e:
        print(f"插入数据时出错: {e}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def query_data():
    """查询数据（基本查询、条件查询、排序、分页）"""
    connection = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        
        if connection.is_connected():
            # 使用字典游标，结果会以字典形式返回
            cursor = connection.cursor(dictionary=True)
            
            # 基本查询
            print("\n----- 所有用户 -----")
            cursor.execute("SELECT * FROM users")
            all_users = cursor.fetchall()
            for user in all_users:
                print(user)
            
            # 条件查询
            print("\n----- 年龄大于28的用户 -----")
            cursor.execute("SELECT * FROM users WHERE age > %s", (28,))
            for user in cursor.fetchall():
                print(user)
            
            # 排序和分页
            print("\n----- 按年龄降序排列，取前2条 -----")
            cursor.execute("SELECT * FROM users ORDER BY age DESC LIMIT 2")
            for user in cursor.fetchall():
                print(user)
                
            # 获取单条记录
            print("\n----- 获取ID为1的用户 -----")
            cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
            user = cursor.fetchone()
            print(user)
            
    except Error as e:
        print(f"查询数据时出错: {e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def update_data():
    """更新数据"""
    connection = None
    try:
        connection = mysql.connector.connect(** DB_CONFIG)
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 更新操作
            update_query = """
            UPDATE users
            SET age = %s, email = %s
            WHERE name = %s
            """
            data = (26, "zhangsan_new@example.com", "张三")
            cursor.execute(update_query, data)
            
            connection.commit()
            print(f"更新了 {cursor.rowcount} 条记录")
            
    except Error as e:
        print(f"更新数据时出错: {e}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def delete_data():
    """删除数据"""
    connection = None
    try:
        connection = mysql.connector.connect(**DB_CONFIG)
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 删除操作
            delete_query = "DELETE FROM users WHERE name = %s"
            cursor.execute(delete_query, ("赵六",))
            
            connection.commit()
            print(f"删除了 {cursor.rowcount} 条记录")
            
    except Error as e:
        print(f"删除数据时出错: {e}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def transaction_demo():
    """事务处理示例"""
    connection = None
    try:
        connection = mysql.connector.connect(** DB_CONFIG)
        connection.autocommit = False  # 关闭自动提交，开启事务
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 事务中的多个操作
            cursor.execute("INSERT INTO users (name, age, email) VALUES (%s, %s, %s)", 
                          ("孙七", 40, "sunqi@example.com"))
            cursor.execute("UPDATE users SET age = 31 WHERE name = %s", ("李四",))
            
            # 手动提交事务
            connection.commit()
            print("事务提交成功")
            
    except Error as e:
        print(f"事务执行出错，将回滚: {e}")
        if connection:
            connection.rollback()  # 回滚事务
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def connection_pool_demo():
    """连接池示例"""
    try:
        # 创建连接池
        pool = pooling.MySQLConnectionPool(
            pool_name="mypool",
            pool_size=5,  # 连接池大小
            **DB_CONFIG
        )
        
        # 从连接池获取连接
        connection = pool.get_connection()
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)
            cursor.execute("SELECT COUNT(*) as total FROM users")
            result = cursor.fetchone()
            print(f"\n连接池查询 - 用户总数: {result['total']}")
            
    except Error as e:
        print(f"连接池操作出错: {e}")
    finally:
        if 'connection' in locals() and connection.is_connected():
            cursor.close()
            connection.close()  # 归还连接到连接池

def main():
    # 执行所有操作
    create_database()
    create_table()
    insert_data()
    query_data()
    update_data()
    delete_data()
    query_data()  # 查看更新和删除后的结果
    transaction_demo()
    connection_pool_demo()

if __name__ == "__main__":
    main()
